Procrastinate Pro+¶

Маркетинговый анализ развлекательного приложения Procrastinate Pro+.

Проблема: несмотря на огромные вложения в рекламу, последние несколько месяцев компания терпит убытки.

Задача: разобраться в причинах и помочь компании выйти в плюс.

Описание данных:

Есть данные о пользователях, привлечённых с 1 мая по 27 октября 2019 года:

  • лог сервера с данными об их посещениях
  • выгрузка их покупок за этот период
  • рекламные расходы

Условие:

На календаре 1 ноября 2019 года, а в бизнес-плане заложено, что пользователи должны окупаться не позднее чем через две недели после привлечения

Table of Contents

  • 1  Загрузка и предобратка данных
  • 2  Функции для расчёта и анализа LTV, ROI, удержания и конверсии
  • 3  Исследовательский анализ данных
  • 4  Маркетинг
  • 5  Окупаемость рекламы
  • 6  Вывод
    • 6.1  Подпункт

Загрузка и предобратка данных¶

In [1]:
import pandas as pd
import seaborn as sns
import numpy as np
import matplotlib.pyplot as plt
from datetime import datetime, timedelta
In [2]:
try:
    visits = pd.read_csv('visits_info_short.csv')
except:
    visits = pd.read_csv('/datasets/visits_info_short.csv')

try:
    orders = pd.read_csv('orders_info_short.csv')
except:
    orders = pd.read_csv('/datasets/orders_info_short.csv')
    
try:
    costs = pd.read_csv('costs_info_short.csv')
except:
    costs = pd.read_csv('/datasets/costs_info_short.csv')

display(visits)
display(orders)
display(costs)
User Id Region Device Channel Session Start Session End
0 981449118918 United States iPhone organic 2019-05-01 02:36:01 2019-05-01 02:45:01
1 278965908054 United States iPhone organic 2019-05-01 04:46:31 2019-05-01 04:47:35
2 590706206550 United States Mac organic 2019-05-01 14:09:25 2019-05-01 15:32:08
3 326433527971 United States Android TipTop 2019-05-01 00:29:59 2019-05-01 00:54:25
4 349773784594 United States Mac organic 2019-05-01 03:33:35 2019-05-01 03:57:40
... ... ... ... ... ... ...
309896 329994900775 UK PC LeapBob 2019-10-31 13:28:12 2019-10-31 14:39:29
309897 334903592310 France PC lambdaMediaAds 2019-10-31 22:14:52 2019-10-31 22:39:36
309898 540102010126 Germany PC organic 2019-10-31 01:40:48 2019-10-31 01:41:31
309899 308736936846 Germany Mac organic 2019-10-31 07:37:34 2019-10-31 07:37:55
309900 109329042535 Germany PC lambdaMediaAds 2019-10-31 14:17:43 2019-10-31 15:17:04

309901 rows × 6 columns

User Id Event Dt Revenue
0 188246423999 2019-05-01 23:09:52 4.99
1 174361394180 2019-05-01 12:24:04 4.99
2 529610067795 2019-05-01 11:34:04 4.99
3 319939546352 2019-05-01 15:34:40 4.99
4 366000285810 2019-05-01 13:59:51 4.99
... ... ... ...
40207 651604369137 2019-10-31 16:19:07 4.99
40208 275341387049 2019-10-31 01:17:17 4.99
40209 374656616484 2019-10-31 06:17:29 4.99
40210 168548862926 2019-10-31 22:46:19 4.99
40211 329994900775 2019-10-31 13:29:06 4.99

40212 rows × 3 columns

dt Channel costs
0 2019-05-01 FaceBoom 113.3
1 2019-05-02 FaceBoom 78.1
2 2019-05-03 FaceBoom 85.8
3 2019-05-04 FaceBoom 136.4
4 2019-05-05 FaceBoom 122.1
... ... ... ...
1795 2019-10-23 lambdaMediaAds 4.0
1796 2019-10-24 lambdaMediaAds 6.4
1797 2019-10-25 lambdaMediaAds 8.8
1798 2019-10-26 lambdaMediaAds 8.8
1799 2019-10-27 lambdaMediaAds 12.0

1800 rows × 3 columns

Приведем названия колонок к рабочему виду

In [3]:
visits.columns = visits.columns.str.lower()
visits = visits.rename(columns = {'user id' : 'user_id', 'session start' : 'session_start', 'session end' : 'session_end'})
orders.columns = orders.columns.str.lower()
orders = orders.rename(columns = {'user id' : 'user_id', 'event dt' : 'event_dt'})
costs.columns = costs.columns.str.lower()
In [4]:
visits.info()
orders.info()
costs.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 309901 entries, 0 to 309900
Data columns (total 6 columns):
 #   Column         Non-Null Count   Dtype 
---  ------         --------------   ----- 
 0   user_id        309901 non-null  int64 
 1   region         309901 non-null  object
 2   device         309901 non-null  object
 3   channel        309901 non-null  object
 4   session_start  309901 non-null  object
 5   session_end    309901 non-null  object
dtypes: int64(1), object(5)
memory usage: 14.2+ MB
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 40212 entries, 0 to 40211
Data columns (total 3 columns):
 #   Column    Non-Null Count  Dtype  
---  ------    --------------  -----  
 0   user_id   40212 non-null  int64  
 1   event_dt  40212 non-null  object 
 2   revenue   40212 non-null  float64
dtypes: float64(1), int64(1), object(1)
memory usage: 942.6+ KB
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1800 entries, 0 to 1799
Data columns (total 3 columns):
 #   Column   Non-Null Count  Dtype  
---  ------   --------------  -----  
 0   dt       1800 non-null   object 
 1   channel  1800 non-null   object 
 2   costs    1800 non-null   float64
dtypes: float64(1), object(2)
memory usage: 42.3+ KB

Пропусков нет. Типы данных, за исключением дат, в соответствующем формате

In [5]:
display(visits.duplicated().sum())
display(orders.duplicated().sum())
display(costs.duplicated().sum())
0
0
0

Явных дубликатов не обнаружено, все ок

In [6]:
display(visits['channel'].value_counts())
display(visits['device'].value_counts())
display(visits['region'].value_counts())
display(costs['channel'].value_counts())
organic               107760
TipTop                 54794
FaceBoom               49022
WahooNetBanner         20465
LeapBob                17013
OppleCreativeMedia     16794
RocketSuperAds         12724
YRabbit                 9053
MediaTornado            8878
AdNonSense              6891
lambdaMediaAds          6507
Name: channel, dtype: int64
iPhone     112603
Android     72590
PC          62686
Mac         62022
Name: device, dtype: int64
United States    207327
UK                36419
France            35396
Germany           30759
Name: region, dtype: int64
LeapBob               180
TipTop                180
WahooNetBanner        180
FaceBoom              180
MediaTornado          180
lambdaMediaAds        180
AdNonSense            180
RocketSuperAds        180
YRabbit               180
OppleCreativeMedia    180
Name: channel, dtype: int64

Неявные дубликаты тоже отсутствуют

In [7]:
visits['session_start'] = pd.to_datetime(visits['session_start'])
visits['session_end'] = pd.to_datetime(visits['session_end'])
orders['event_dt'] = pd.to_datetime(orders['event_dt'])
costs['dt'] = pd.to_datetime(costs['dt']).dt.date

Приведем даты к формату datetime

Функции для расчёта и анализа LTV, ROI, удержания и конверсии¶

Добавим необходимые функции, полученные в ходе обучения в спринте, слегка изменив их

In [8]:
# функция для создания пользовательских профилей

def get_profiles(sessions, orders, ad_costs):

    # находим параметры первых посещений
    profiles = (
        sessions.sort_values(by=['user_id', 'session_start'])
        .groupby('user_id')
        .agg(
            {
                'session_start': 'first',
                'channel': 'first',
                'device': 'first',
                'region': 'first',
            }
        )
        .rename(columns={'session_start': 'first_ts'})
        .reset_index()
    )

    # для когортного анализа определяем дату первого посещения
    # и первый день месяца, в который это посещение произошло
    profiles['dt'] = profiles['first_ts'].dt.date
    profiles['month'] = profiles['first_ts'].astype('datetime64[M]')

    # добавляем признак платящих пользователей
    profiles['payer'] = profiles['user_id'].isin(orders['user_id'].unique())

    

    # считаем количество уникальных пользователей
    # с одинаковыми источником и датой привлечения
    new_users = (
        profiles.groupby(['dt', 'channel'])
        .agg({'user_id': 'nunique'})
        .rename(columns={'user_id': 'unique_users'})
        .reset_index()
    )

    # объединяем траты на рекламу и число привлечённых пользователей
    ad_costs = ad_costs.merge(new_users, on=['dt', 'channel'], how='left')

    # делим рекламные расходы на число привлечённых пользователей
    ad_costs['acquisition_cost'] = ad_costs['costs'] / ad_costs['unique_users']

    # добавляем стоимость привлечения в профили
    profiles = profiles.merge(
        ad_costs[['dt', 'channel', 'acquisition_cost']],
        on=['dt', 'channel'],
        how='left',
    )

    # стоимость привлечения органических пользователей равна нулю
    profiles['acquisition_cost'] = profiles['acquisition_cost'].fillna(0)

    return profiles
In [9]:
# функция для расчёта удержания

def get_retention(
    profiles,
    sessions,
    observation_date,
    horizon_days,
    dimensions=[],
    ignore_horizon=False,
):

    # добавляем столбец payer в передаваемый dimensions список
    dimensions = ['payer'] + dimensions

    # исключаем пользователей, не «доживших» до горизонта анализа
    last_suitable_acquisition_date = observation_date
    if not ignore_horizon:
        last_suitable_acquisition_date = observation_date - timedelta(
            days=horizon_days - 1
        )
    result_raw = profiles.query('dt <= @last_suitable_acquisition_date')

    # собираем «сырые» данные для расчёта удержания
    result_raw = result_raw.merge(
        sessions[['user_id', 'session_start']], on='user_id', how='left'
    )
    result_raw['lifetime'] = (
        result_raw['session_start'] - result_raw['first_ts']
    ).dt.days

    # функция для группировки таблицы по желаемым признакам
    def group_by_dimensions(df, dims, horizon_days):
        result = df.pivot_table(
            index=dims, columns='lifetime', values='user_id', aggfunc='nunique'
        )
        cohort_sizes = (
            df.groupby(dims)
            .agg({'user_id': 'nunique'})
            .rename(columns={'user_id': 'cohort_size'})
        )
        result = cohort_sizes.merge(result, on=dims, how='left').fillna(0)
        result = result.div(result['cohort_size'], axis=0)
        result = result[['cohort_size'] + list(range(horizon_days))]
        result['cohort_size'] = cohort_sizes
        return result

    # получаем таблицу удержания
    result_grouped = group_by_dimensions(result_raw, dimensions, horizon_days)

    # получаем таблицу динамики удержания
    result_in_time = group_by_dimensions(
        result_raw, dimensions + ['dt'], horizon_days
    )

    # возвращаем обе таблицы и сырые данные
    return result_raw, result_grouped, result_in_time 
In [10]:
# функция для расчёта конверсии

def get_conversion(
    profiles,
    purchases,
    observation_date,
    horizon_days,
    dimensions=[],
    ignore_horizon=False,
):

    # исключаем пользователей, не «доживших» до горизонта анализа
    last_suitable_acquisition_date = observation_date
    if not ignore_horizon:
        last_suitable_acquisition_date = observation_date - timedelta(
            days=horizon_days - 1
        )
    result_raw = profiles.query('dt <= @last_suitable_acquisition_date')

    # определяем дату и время первой покупки для каждого пользователя
    first_purchases = (
        purchases.sort_values(by=['user_id', 'event_dt'])
        .groupby('user_id')
        .agg({'event_dt': 'first'})
        .reset_index()
    )

    # добавляем данные о покупках в профили
    result_raw = result_raw.merge(
        first_purchases[['user_id', 'event_dt']], on='user_id', how='left'
    )

    # рассчитываем лайфтайм для каждой покупки
    result_raw['lifetime'] = (
        result_raw['event_dt'] - result_raw['first_ts']
    ).dt.days

    # группируем по cohort, если в dimensions ничего нет
    if len(dimensions) == 0:
        result_raw['cohort'] = 'All users' 
        dimensions = dimensions + ['cohort']

    # функция для группировки таблицы по желаемым признакам
    def group_by_dimensions(df, dims, horizon_days):
        result = df.pivot_table(
            index=dims, columns='lifetime', values='user_id', aggfunc='nunique'
        )
        result = result.fillna(0).cumsum(axis = 1)
        cohort_sizes = (
            df.groupby(dims)
            .agg({'user_id': 'nunique'})
            .rename(columns={'user_id': 'cohort_size'})
        )
        result = cohort_sizes.merge(result, on=dims, how='left').fillna(0)
        # делим каждую «ячейку» в строке на размер когорты
        # и получаем conversion rate
        result = result.div(result['cohort_size'], axis=0)
        result = result[['cohort_size'] + list(range(horizon_days))]
        result['cohort_size'] = cohort_sizes
        return result

    # получаем таблицу конверсии
    result_grouped = group_by_dimensions(result_raw, dimensions, horizon_days)

    # для таблицы динамики конверсии убираем 'cohort' из dimensions
    if 'cohort' in dimensions: 
        dimensions = []

    # получаем таблицу динамики конверсии
    result_in_time = group_by_dimensions(
        result_raw, dimensions + ['dt'], horizon_days
    )

    # возвращаем обе таблицы и сырые данные
    return result_raw, result_grouped, result_in_time 
In [11]:
# функция для расчёта LTV и ROI

def get_ltv(
    profiles,
    purchases,
    observation_date,
    horizon_days,
    dimensions=[],
    ignore_horizon=False,
):

    # исключаем пользователей, не «доживших» до горизонта анализа
    last_suitable_acquisition_date = observation_date
    if not ignore_horizon:
        last_suitable_acquisition_date = observation_date - timedelta(
            days=horizon_days - 1
        )
    result_raw = profiles.query('dt <= @last_suitable_acquisition_date')
    # добавляем данные о покупках в профили
    result_raw = result_raw.merge(
        purchases[['user_id', 'event_dt', 'revenue']], on='user_id', how='left'
    )
    # рассчитываем лайфтайм пользователя для каждой покупки
    result_raw['lifetime'] = (
        result_raw['event_dt'] - result_raw['first_ts']
    ).dt.days
    # группируем по cohort, если в dimensions ничего нет
    if len(dimensions) == 0:
        result_raw['cohort'] = 'All users'
        dimensions = dimensions + ['cohort']

    # функция группировки по желаемым признакам
    def group_by_dimensions(df, dims, horizon_days):
        # строим «треугольную» таблицу выручки
        result = df.pivot_table(
            index=dims, columns='lifetime', values='revenue', aggfunc='sum'
        )
        # находим сумму выручки с накоплением
        result = result.fillna(0).cumsum(axis=1)
        # вычисляем размеры когорт
        cohort_sizes = (
            df.groupby(dims)
            .agg({'user_id': 'nunique'})
            .rename(columns={'user_id': 'cohort_size'})
        )
        # объединяем размеры когорт и таблицу выручки
        result = cohort_sizes.merge(result, on=dims, how='left').fillna(0)
        # считаем LTV: делим каждую «ячейку» в строке на размер когорты
        result = result.div(result['cohort_size'], axis=0)
        # исключаем все лайфтаймы, превышающие горизонт анализа
        result = result[['cohort_size'] + list(range(horizon_days))]
        # восстанавливаем размеры когорт
        result['cohort_size'] = cohort_sizes

        # собираем датафрейм с данными пользователей и значениями CAC, 
        # добавляя параметры из dimensions
        cac = df[['user_id', 'acquisition_cost'] + dims].drop_duplicates()

        # считаем средний CAC по параметрам из dimensions
        cac = (
            cac.groupby(dims)
            .agg({'acquisition_cost': 'mean'})
            .rename(columns={'acquisition_cost': 'cac'})
        )

        # считаем ROI: делим LTV на CAC
        roi = result.div(cac['cac'], axis=0)

        # удаляем строки с бесконечным ROI
        roi = roi[~roi['cohort_size'].isin([np.inf])]

        # восстанавливаем размеры когорт в таблице ROI
        roi['cohort_size'] = cohort_sizes

        # добавляем CAC в таблицу ROI
        roi['cac'] = cac['cac']

        # в финальной таблице оставляем размеры когорт, CAC
        # и ROI в лайфтаймы, не превышающие горизонт анализа
        roi = roi[['cohort_size', 'cac'] + list(range(horizon_days))]

        # возвращаем таблицы LTV и ROI
        return result, roi

    # получаем таблицы LTV и ROI
    result_grouped, roi_grouped = group_by_dimensions(
        result_raw, dimensions, horizon_days
    )

    # для таблиц динамики убираем 'cohort' из dimensions
    if 'cohort' in dimensions:
        dimensions = []

    # получаем таблицы динамики LTV и ROI
    result_in_time, roi_in_time = group_by_dimensions(
        result_raw, dimensions + ['dt'], horizon_days
    )

    return (
        result_raw,  # сырые данные
        result_grouped,  # таблица LTV
        result_in_time,  # таблица динамики LTV
        roi_grouped,  # таблица ROI
        roi_in_time,  # таблица динамики ROI
    ) 
In [12]:
# функция для сглаживания фрейма

def filter_data(df, window):
    # для каждого столбца применяем скользящее среднее
    for column in df.columns.values:
        df[column] = df[column].rolling(window).mean() 
    return df 
In [13]:
# функция для визуализации удержания

def plot_retention(retention, retention_history, horizon, window=7):

    # задаём размер сетки для графиков
    plt.figure(figsize=(15, 10))

    # исключаем размеры когорт и удержание первого дня
    retention = retention.drop(columns=['cohort_size', 0])
    # в таблице динамики оставляем только нужный лайфтайм
    retention_history = retention_history.drop(columns=['cohort_size'])[
        [horizon - 1]
    ]

    # если в индексах таблицы удержания только payer,
    # добавляем второй признак — cohort
    if retention.index.nlevels == 1:
        retention['cohort'] = 'All users'
        retention = retention.reset_index().set_index(['cohort', 'payer'])

    # в таблице графиков — два столбца и две строки, четыре ячейки
    # в первой строим кривые удержания платящих пользователей
    ax1 = plt.subplot(2, 2, 1)
    retention.query('payer == True').droplevel('payer').T.plot(
        grid=True, ax=ax1
    )
    plt.legend()
    plt.xlabel('Лайфтайм')
    plt.title('Удержание платящих пользователей')

    # во второй ячейке строим кривые удержания неплатящих
    # вертикальная ось — от графика из первой ячейки
    ax2 = plt.subplot(2, 2, 2, sharey=ax1)
    retention.query('payer == False').droplevel('payer').T.plot(
        grid=True, ax=ax2
    )
    plt.legend()
    plt.xlabel('Лайфтайм')
    plt.title('Удержание неплатящих пользователей')

    # в третьей ячейке — динамика удержания платящих
    ax3 = plt.subplot(2, 2, 3)
    # получаем названия столбцов для сводной таблицы
    columns = [
        name
        for name in retention_history.index.names
        if name not in ['dt', 'payer']
    ]
    # фильтруем данные и строим график
    filtered_data = retention_history.query('payer == True').pivot_table(
        index='dt', columns=columns, values=horizon - 1, aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax3)
    plt.xlabel('Дата привлечения')
    plt.title(
        'Динамика удержания платящих пользователей на {}-й день'.format(
            horizon
        )
    )

    # в чётвертой ячейке — динамика удержания неплатящих
    ax4 = plt.subplot(2, 2, 4, sharey=ax3)
    # фильтруем данные и строим график
    filtered_data = retention_history.query('payer == False').pivot_table(
        index='dt', columns=columns, values=horizon - 1, aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax4)
    plt.xlabel('Дата привлечения')
    plt.title(
        'Динамика удержания неплатящих пользователей на {}-й день'.format(
            horizon
        )
    )
    
    plt.tight_layout()
    plt.show() 
In [14]:
# функция для визуализации конверсии

def plot_conversion(conversion, conversion_history, horizon, window=7):

    # задаём размер сетки для графиков
    plt.figure(figsize=(15, 5))

    # исключаем размеры когорт
    conversion = conversion.drop(columns=['cohort_size'])
    # в таблице динамики оставляем только нужный лайфтайм
    conversion_history = conversion_history.drop(columns=['cohort_size'])[
        [horizon - 1]
    ]

    # первый график — кривые конверсии
    ax1 = plt.subplot(1, 2, 1)
    conversion.T.plot(grid=True, ax=ax1)
    plt.legend()
    plt.xlabel('Лайфтайм')
    plt.title('Конверсия пользователей')

    # второй график — динамика конверсии
    ax2 = plt.subplot(1, 2, 2, sharey=ax1)
    columns = [
        # столбцами сводной таблицы станут все столбцы индекса, кроме даты
        name for name in conversion_history.index.names if name not in ['dt']
    ]
    filtered_data = conversion_history.pivot_table(
        index='dt', columns=columns, values=horizon - 1, aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax2)
    plt.xlabel('Дата привлечения')
    plt.title('Динамика конверсии пользователей на {}-й день'.format(horizon))

    plt.tight_layout()
    plt.show() 
In [15]:
# функция для визуализации LTV и ROI

def plot_ltv_roi(ltv, ltv_history, roi, roi_history, horizon, window=7):

    # задаём сетку отрисовки графиков
    plt.figure(figsize=(20, 10))

    # из таблицы ltv исключаем размеры когорт
    ltv = ltv.drop(columns=['cohort_size'])
    # в таблице динамики ltv оставляем только нужный лайфтайм
    ltv_history = ltv_history.drop(columns=['cohort_size'])[[horizon - 1]]

    # стоимость привлечения запишем в отдельный фрейм
    cac_history = roi_history[['cac']]

    # из таблицы roi исключаем размеры когорт и cac
    roi = roi.drop(columns=['cohort_size', 'cac'])
    # в таблице динамики roi оставляем только нужный лайфтайм
    roi_history = roi_history.drop(columns=['cohort_size', 'cac'])[
        [horizon - 1]
    ]

    # первый график — кривые ltv
    ax1 = plt.subplot(2, 3, 1)
    ltv.T.plot(grid=True, ax=ax1)
    plt.legend()
    plt.xlabel('Лайфтайм')
    plt.title('LTV')

    # второй график — динамика ltv
    ax2 = plt.subplot(2, 3, 2, sharey=ax1)
    # столбцами сводной таблицы станут все столбцы индекса, кроме даты
    columns = [name for name in ltv_history.index.names if name not in ['dt']]
    filtered_data = ltv_history.pivot_table(
        index='dt', columns=columns, values=horizon - 1, aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax2)
    plt.xlabel('Дата привлечения')
    plt.title('Динамика LTV пользователей на {}-й день'.format(horizon))

    # третий график — динамика cac
    ax3 = plt.subplot(2, 3, 3, sharey=ax1)
    # столбцами сводной таблицы станут все столбцы индекса, кроме даты
    columns = [name for name in cac_history.index.names if name not in ['dt']]
    filtered_data = cac_history.pivot_table(
        index='dt', columns=columns, values='cac', aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax3)
    plt.xlabel('Дата привлечения')
    plt.title('Динамика стоимости привлечения пользователей')

    # четвёртый график — кривые roi
    ax4 = plt.subplot(2, 3, 4)
    roi.T.plot(grid=True, ax=ax4)
    plt.axhline(y=1, color='red', linestyle='--', label='Уровень окупаемости')
    plt.legend()
    plt.xlabel('Лайфтайм')
    plt.title('ROI')

    # пятый график — динамика roi
    ax5 = plt.subplot(2, 3, 5, sharey=ax4)
    # столбцами сводной таблицы станут все столбцы индекса, кроме даты
    columns = [name for name in roi_history.index.names if name not in ['dt']]
    filtered_data = roi_history.pivot_table(
        index='dt', columns=columns, values=horizon - 1, aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax5)
    plt.axhline(y=1, color='red', linestyle='--', label='Уровень окупаемости')
    plt.xlabel('Дата привлечения')
    plt.title('Динамика ROI пользователей на {}-й день'.format(horizon))

    plt.tight_layout()
    plt.show() 

Исследовательский анализ данных¶

Получим профили пользователей

In [16]:
profiles = get_profiles(visits, orders, costs)
profiles
Out[16]:
user_id first_ts channel device region dt month payer acquisition_cost
0 599326 2019-05-07 20:58:57 FaceBoom Mac United States 2019-05-07 2019-05-01 True 1.088172
1 4919697 2019-07-09 12:46:07 FaceBoom iPhone United States 2019-07-09 2019-07-01 False 1.107237
2 6085896 2019-10-01 09:58:33 organic iPhone France 2019-10-01 2019-10-01 False 0.000000
3 22593348 2019-08-22 21:35:48 AdNonSense PC Germany 2019-08-22 2019-08-01 False 0.988235
4 31989216 2019-10-02 00:07:44 YRabbit iPhone United States 2019-10-02 2019-10-01 False 0.230769
... ... ... ... ... ... ... ... ... ...
150003 999956196527 2019-09-28 08:33:02 TipTop iPhone United States 2019-09-28 2019-09-01 False 3.500000
150004 999975439887 2019-10-21 00:35:17 organic PC UK 2019-10-21 2019-10-01 False 0.000000
150005 999976332130 2019-07-23 02:57:06 TipTop iPhone United States 2019-07-23 2019-07-01 False 2.600000
150006 999979924135 2019-09-28 21:28:09 MediaTornado PC United States 2019-09-28 2019-09-01 False 0.205714
150007 999999563947 2019-10-18 19:57:25 organic iPhone United States 2019-10-18 2019-10-01 False 0.000000

150008 rows × 9 columns

Определим минимальную и максимальную даты привлечения пользователей

In [17]:
min_date = profiles['dt'].min()
max_date = profiles['dt'].max()
print(f' Минимальная дата привлечения: {min_date} \n Максимальная дата привлечения: {max_date}')
 Минимальная дата привлечения: 2019-05-01 
 Максимальная дата привлечения: 2019-10-27

Даты привлечения пользователей, полученные из датафрейма, соответствуют условиям проекта

Выясним, из каких стран пользователи приходят в приложение и на какую страну приходится больше всего платящих пользователей

In [18]:
(profiles.groupby('region').agg({'user_id' : 'count', 'payer' : 'mean'}).
     rename(columns = {'user_id' : 'count', 'payer' : 'mean'}).
         sort_values(by = 'mean', ascending = False).
             style.format({'count' : '{:.0f}', 'mean': '{:,.2%}'.format}))
Out[18]:
count mean
region
United States 100002 6.90%
Germany 14981 4.11%
UK 17575 3.98%
France 17450 3.80%

Большая часть пользователей приложения из США. Среди жителей Америки также наибольшая доля платящих пользователей

Узнаем, какими устройствами пользуются клиенты и какие устройства предпочитают платящие пользователи

In [19]:
(profiles.groupby('device').agg({'user_id' : 'count', 'payer' : 'mean'}).
     rename(columns = {'user_id' : 'count', 'payer' : 'mean'}).
         sort_values(by = 'mean', ascending = False).
             style.format({'count' : '{:.0f}', 'mean': '{:,.2%}'.format}))
Out[19]:
count mean
device
Mac 30042 6.36%
iPhone 54479 6.21%
Android 35032 5.85%
PC 30455 5.05%

Посетителей с айфонами больше всего. Также пользователи apple предпочитают платить немногим больше, нежели пользователи android или ПК

Изучим рекламные источники привлечения и определим каналы, из которых пришло больше всего платящих пользователей

In [20]:
(profiles.groupby('channel').agg({'user_id' : 'count', 'payer' : 'mean'}).
     rename(columns = {'user_id' : 'count', 'payer' : 'mean'}).
         sort_values(by = 'mean', ascending = False).
             style.format({'count' : '{:.0f}', 'mean': '{:,.2%}'.format}))
Out[20]:
count mean
channel
FaceBoom 29144 12.20%
AdNonSense 3880 11.34%
lambdaMediaAds 2149 10.47%
TipTop 19561 9.60%
RocketSuperAds 4448 7.91%
WahooNetBanner 8553 5.30%
YRabbit 4312 3.83%
MediaTornado 4364 3.57%
LeapBob 8553 3.06%
OppleCreativeMedia 8605 2.71%
organic 56439 2.06%

Больше всего пришло пользователей из FaceBoom и TipTop. Самую высокую конверсию показывают Faceboom, AdNonSense и lambdaMediaAds

Маркетинг¶

Посчитаем общую сумму расходов на маркетинг

Выясним, как траты распределены по рекламным источникам

In [21]:
total_costs = costs['costs'].sum()
cost_by_channel = costs.groupby('channel').agg({'costs' : 'sum'}).sort_values(by = 'costs', ascending = False)
cost_by_channel['percent'] = cost_by_channel['costs'] / total_costs
cost_by_channel = cost_by_channel.style.format({'costs' : '{:.2f}','percent': '{:,.2%}'.format})
print(f'Общая сумма расходов на маркетинг:  {total_costs.round()} $')
cost_by_channel
Общая сумма расходов на маркетинг:  105497.0 $
Out[21]:
costs percent
channel
TipTop 54751.30 51.90%
FaceBoom 32445.60 30.75%
WahooNetBanner 5151.00 4.88%
AdNonSense 3911.25 3.71%
OppleCreativeMedia 2151.25 2.04%
RocketSuperAds 1833.00 1.74%
LeapBob 1797.60 1.70%
lambdaMediaAds 1557.60 1.48%
MediaTornado 954.48 0.90%
YRabbit 944.22 0.90%

Чуть больше половины всего рекламного бюджета ушло в TipTop.

Построим визуализацию динамики изменения расходов во времени по каждому источнику

In [22]:
(costs.pivot_table(index = 'dt', columns = 'channel', values = 'costs', aggfunc = 'mean').
     plot(figsize = (25, 10), grid = True))
plt.ylabel('Расходы на маркетинг, $')
plt.xlabel('Дата привлечения')
plt.title('Динамика расходов по каналам привлечения')
plt.show()
In [23]:
costs['week'] = pd.to_datetime(costs['dt']).dt.isocalendar().week
(costs.pivot_table(index = 'week', columns = 'channel', values = 'costs', aggfunc = 'mean').
     plot(figsize = (25, 10), grid = True))
xtick_location = costs['week'].tolist()[::1]
plt.ylabel('Расходы на маркетинг, $')
plt.xlabel('Номер недели привлечения')
plt.title('Динамика расходов по каналам привлечения по неделям')
plt.xticks(ticks=xtick_location)
plt.show()

На графике видно, что с Июня сильно выросли маркетинговые расходы в TipTop и Faceboom

Узнаем, сколько в среднем стоило привлечение одного пользователя (CAC) из каждого источника

In [24]:
(profiles.groupby('channel').agg({'acquisition_cost' : 'mean'}).
 sort_values(by = 'acquisition_cost', ascending = False).
    plot(kind = 'bar', grid = True, legend = None))
plt.ylabel('Стоимость привлечения одного клиента, $')
plt.xlabel('Канал привлечения')
plt.title('Стоимость привлечения одного клиента по каналам привлечения')
plt.xticks(rotation=50, horizontalalignment='right')
plt.show()

Привлечение одного пользователя из TipTop значительно дороже привлечения из всех остальных каналов.

Окупаемость рекламы¶

По условию дата анализа - 2019-11-01, а срок окупаемости привлечения одного клиента - 14 дней

In [25]:
observation_date = datetime(2019, 11, 1).date()
analysis_horizon = 14
profiles = profiles.query('channel != "organic"')

Построим графики LTV и ROI, а также графики динамики LTV, CAC и ROI

In [26]:
ltv_raw, ltv, ltv_history, roi, roi_history = get_ltv(
    profiles,
    orders,
    observation_date,
    analysis_horizon,
    ignore_horizon=False,
)
plot_ltv_roi(ltv, ltv_history, roi, roi_history, analysis_horizon, window=7)

По графикам можно сделать такие выводы:

  • Реклама не окупается. ROI в конце двух недель — около 80%.
  • CAC резко растет с Июня месяца, а ROI в тот же период начинает снижаться. Вероятно, дело в чрезмерном увеличении рекламного бюджета.
  • LTV достаточно стабилен. Значит, дело не в ухудшении качества пользователей.

Построим и изучим графики конверсии и удержания

In [27]:
conversion_raw, conversion, conversion_history = get_conversion(profiles, 
                                                                orders, 
                                                                observation_date, 
                                                                analysis_horizon, 
                                                                ignore_horizon=False)
plot_conversion(conversion, conversion_history, analysis_horizon, window=7)

После увеличения рекламного бюджета в Июне показатель конверсии заметно вырос

In [28]:
retention_raw, retention, retention_history = get_retention(profiles, 
                                                            visits, 
                                                            observation_date, 
                                                            analysis_horizon,
                                                            ignore_horizon=False)
plot_retention(retention, retention_history, analysis_horizon, window=7)

Динамика удержания пользователей довольно стабильна Чтобы разобраться в причинах неокупаемости рекламы, пройдём по всем доступным характеристикам пользователей — стране, источнику и устройству первого посещения.

Начнем с разбивки по типу устройства

In [29]:
dimensions = ['device']
ltv_raw, ltv, ltv_history, roi, roi_history = get_ltv(
    profiles,
    orders,
    observation_date,
    analysis_horizon,
    dimensions = dimensions,
    ignore_horizon=False,
)
plot_ltv_roi(ltv, ltv_history, roi, roi_history, analysis_horizon, window=7)

После увелечения расходов на маркетинг, привлечение пользователей Iphone и Mac стало значительно дороже, чем пользователей Android и ПК. После двух недель, вторая группа достигает чуть больших показателей ROI, в то время как яблочники не преодолевают отметку в 80%. Вряд ли маркетологов устраивает такая окупаемость. Также с Июня ROI стабильно снижается для всех типов устройств

Посмотрим в разбике по странам

In [30]:
dimensions = ['region']
ltv_raw, ltv, ltv_history, roi, roi_history = get_ltv(
    profiles,
    orders,
    observation_date,
    analysis_horizon,
    dimensions = dimensions,
    ignore_horizon=False,
)
plot_ltv_roi(ltv, ltv_history, roi, roi_history, analysis_horizon, window=7)

Вот и первая проблема. Пользователи из США обходятся слишком дорого и не окупаются за 2 недели, в то время как пользователи из других стран окупаются уже через одну неделю. После увеличения затрат на рекламу, ROI пользователей из штатов не преодолевают отметку в 100%, даже несмотря на то, что их LTV выше чем у пользователей из других стран

In [31]:
conversion_raw, conversion, conversion_history = get_conversion(profiles, 
                                                                orders, 
                                                                observation_date, 
                                                                analysis_horizon, 
                                                                dimensions = dimensions,
                                                                ignore_horizon=False)
plot_conversion(conversion, conversion_history, analysis_horizon, window=7)
In [32]:
retention_raw, retention, retention_history = get_retention(profiles, 
                                                            visits, 
                                                            observation_date, 
                                                            analysis_horizon, 
                                                            dimensions = dimensions,
                                                            ignore_horizon=False)
plot_retention(retention, retention_history, analysis_horizon, window=7)

Увеличив в Июне расходы на привлечение пользователей из США, действительно удалось получить хорошие показатели конверсии, но удержание значительно хуже по сравнению с остальными

Посмотрим на разбивку по источникам привлечения

In [33]:
dimensions = ['channel']
ltv_raw, ltv, ltv_history, roi, roi_history = get_ltv(
    profiles,
    orders,
    observation_date,
    analysis_horizon,
    dimensions = dimensions,
    ignore_horizon=False,
)
plot_ltv_roi(ltv, ltv_history, roi, roi_history, analysis_horizon, window=7)

Вот и вторая проблема. Пользователи, пришедшие из TipTop, FaceBoom и AdNonSense не окупаются, их ROI меньше 100%. Из расчетов выше, мы знаем, что на TipTop и FaceBoom приходится большая часть бюджета на маркетинг. С Июня месяца стоимость привлечения пользователей из TipTop стала сильно дороже, а их ROI чуть больше 50%, что, наверняка, несет много убытков комании.

Вероятно, стоит присмотреться к привлечению пользователей из YRabbit, которые окупаются во много раз, или к пользователям из lambdaMediaAds, чье LTV значительно выше остальных

Узнаем, в чём причина неокупаемости по вышеуказанным проблемным каналам привлечения: в низкой конверсии или низком удержании.

In [34]:
conversion_raw, conversion, conversion_history = get_conversion(profiles, 
                                                                orders, 
                                                                observation_date, 
                                                                analysis_horizon, 
                                                                dimensions = dimensions,
                                                                ignore_horizon=False)
plot_conversion(conversion, conversion_history, analysis_horizon, window=7)
In [35]:
retention_raw, retention, retention_history = get_retention(profiles, 
                                                            visits, 
                                                            observation_date, 
                                                            analysis_horizon, 
                                                            dimensions = dimensions,
                                                            ignore_horizon=False)
plot_retention(retention, retention_history, analysis_horizon, window=7)

Наши проблемные FaceBoom и AdNonSense показывают очень хорошую конверсию, но в то же время у них катастрофически низкое удержание. У TipTop такой проблемы нет, конверсия и удержание на нормальном уровне.

Вывод¶

Проанализировав показатели конверсии, удержании, LTV, CAC и ROI в целом и в разбивке по стране, источнику и устройству первого посещения обнаружились следующие вероятные проблемы в маркетинговой политике компании:

По типу устройства:

  • Во всех категориях LTV примерно одинаков, но привлечение пользователей продукции Apple значительно дороже, чем пользователей Android или ПК. Это может быть связано с разбивкой по регионам, т.к. в США большинство людей пользуется продукцией Apple.

По региону:

  • В Июне сильно увеличились расходы на пользователей из США. Несмотря на их высокую конверсию и LTV, удержание заметно ниже по сравнению с пользователями из других стран. В следствие этих факторов ROI не достигает 100% и компания недополучает прибыль.

По каналу привлечения:

  • Не окупаются пользователи, пришедшие из TipTop, FaceBoom и AdNonSense.
    • Первое объясняется тем, что в Июне резко увеличились расходы для данного источника трафика, а стоимость привлечения одного пользователя значительно выше чем из других источников. В результате ROI чуть больше 50%. Это очень дорогой канал привлечения, который не окупается.
    • Что касается пользователей, привлеченных из FaceBoom и AdNonSense, ROI также не переваливает за 100%. Эти клиенты имеют хорошие показатели конверсии, но в то же время и крайне низкое удержание. Можно сказать, что пользователи из этих источников низкого качества и быстро уходят.

Итого мы видим, что у пользователей из США и из источников FaceBoom и AdNonSense довольно хорошие показатели конверсии, но удержание сильно проседает. Трафик из данных каналов нам не подходят, пользователи не готовы продолжительно платить за наш продукт. Также стоит пересмотреть политику в отношении затрат на TipTop. Привлечение одного пользователя слишком дорогое, а его LTV ничем не примечательна, в следствие чего пользователь оттуда совершенно невыгоден. Это дорогой канал, тянущий деньги.

Я бы рекомендовал обратить внимание на пользователей из европейских стран, которые в данный момент окупаются уже через неделю и удерживаются лучше Американцев. В части источников трафика стоит посмотреть на такие каналы как:

  • YRabbit
  • MediaTornado
  • lambdaMediaAds
  • LeapBob
  • RocketSuperAds

Пользователи с этих каналов более высокого качества и стабильно окупаются более чем в два раза, а зачастую и значительно больше

In [ ]: